Covering Index

contents

쿼리 최적화의 "성배(Holy Grail)"로 불리는 커버링 인덱스(Covering Index) 에 대해 알아보겠습니다.

1. 커버링 인덱스란?

커버링 인덱스는 쿼리를 충족시키는 데 필요한 모든 컬럼을 가지고 있는 인덱스를 말합니다.

• 일반적인 방식: 데이터베이스가 인덱스를 확인하여 행(Row)의 주소를 찾은 다음, 실제 데이터가 있는 테이블 저장소(디스크/힙)로 점프하여 데이터를 가져옵니다.
• 커버링 방식: 데이터베이스가 인덱스를 확인했더니 원하는 데이터가 인덱스 안에 다 있습니다. 테이블 저장소로 갈 필요가 없습니다.

왜 빠른가요?

데이터를 찾기 위해 여기저기 디스크를 뒤지는 "랜덤 I/O" 과정을 없애고, 메모리에서 순차적으로 읽기만 하면 되기 때문입니다. 일종의 '초고속 지름길'입니다.

2. 비유: "도서관 검색대"

도서관에서 "해리 포터" 책의 출판 연도를 알고 싶다고 가정해 봅시다.

• 시나리오 A: 비-커버링 인덱스 (일반적)

  1. 검색대 컴퓨터(인덱스)로 갑니다.
  2. "해리 포터"를 검색합니다. 결과: "4번 서가, B칸".
  3. 4번 서가(테이블/디스크)까지 걸어갑니다.
  4. 책을 꺼내 저작권 페이지에서 연도를 확인합니다.
  • 소요 시간: 5분.

• 시나리오 B: 커버링 인덱스

  1. 검색대 컴퓨터로 갑니다.
  2. "해리 포터"를 검색합니다.
  3. 화면(인덱스)에 "출판 연도: 1997"이라고 이미 적혀 있습니다.
  4. 숫자만 적고 바로 나갑니다. 서가로 걸어갈 필요가 없습니다.
  • 소요 시간: 30초.

3. EXPLAIN에서 확인하는 법

쿼리가 커버링 인덱스를 제대로 타고 있다면, MySQL EXPLAIN 계획의 Extra 컬럼에 다음과 같이 표시됩니다.

• Using index: 성공 메시지입니다. "인덱스 트리 안에서 필요한 모든 걸 찾았다"는 뜻입니다.

주의: type: index(느린 전체 인덱스 스캔)와 혼동하지 마세요. Extra: Using index가 나와야 합니다.

4. 실전 예시

테이블: Orders (id, customer_id, product_id, amount, order_date)

인덱스: CREATE INDEX idx_cust_date ON Orders (customer_id, order_date);

쿼리 A: 커버링 안 됨 (느림)

SELECT amount FROM Orders 
WHERE customer_id = 101 
AND order_date = '2024-01-01';

• 과정:
  1. idx_cust_date를 이용해 customer_id 101번을 찾습니다.
  2. Key Lookup(키 룩업): 쿼리가 amount를 원합니다. 하지만 인덱스에는 amount가 없습니다.
  3. DB는 amount를 가져오기 위해 원본 테이블로 점프해야 합니다.

쿼리 B: 커버링 됨 (빠름)

SELECT order_date FROM Orders 
WHERE customer_id = 101;

• 과정:
  1. idx_cust_date를 이용해 customer_id 101번을 찾습니다.
  2. 쿼리가 order_date를 원합니다. 이것은 인덱스 안에 이미 있습니다.
  3. 즉시 결과를 반환합니다.

5. 커버링 인덱스 만드는 법 (INCLUDE 절)

만약 쿼리 A(amount 조회)도 빠르게 만들고 싶다면 어떻게 할까요?

두 가지 방법이 있습니다.

방법 1: 키에 추가하기 (MySQL 표준)

-- 데이터가 고객 -> 날짜 -> 금액 순으로 정렬됨
CREATE INDEX idx_covering ON Orders (customer_id, order_date, amount);

방법 2: INCLUDE 절 사용 (PostgreSQL / SQL Server)

이 방법이 더 효율적입니다. amount를 인덱스의 "리프 노드(Leaf Node)"에만 덧붙여 저장하고, 정렬에는 사용하지 않습니다. 인덱스 트리를 더 작고 깔끔하게 유지할 수 있습니다.

CREATE INDEX idx_covering 
ON Orders (customer_id, order_date) 
INCLUDE (amount); 

6. 장단점

references